/***********************************************************************
* sp Name: dbo.sp_po_futures_order_remove_duplicate
* Created: 20101002, JDR
* Purpose: establece el orden consecutivo del
*	campo futures_order para las po de version de un periiodo de futuros
*	en especifico
***********************************************************************/
ALTER PROCEDURE sp_po_futures_order_remove_duplicate
	@futurePeriodVersionId	INT, 
	@DEBUG					INT = 0
AS

/*
	error handling
*/
BEGIN TRY
	DECLARE
		@TranStarted BIT
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 ) BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END ELSE BEGIN
		SET @TranStarted = 0
	END

	SET NOCOUNT ON
-------------------------------------------------------
--[BEGIN] SP section
DECLARE @text VARCHAR(512)

SET @text = '	------ Ejecutando sp_po_futures_order_remove_duplicate ' 
		+ CONVERT(VARCHAR(10), @futurePeriodVersionId)

IF @DEBUG = 1
	PRINT @text

DECLARE 
	@purchase_order_version_id	INT, 
	@futures_order				INT,
	@recordDuplicate			INT

SET @recordDuplicate = 0
declare cur_purchasesOrders cursor for
select	purchase_order_version_id,
		futures_order
from purchases_orders_versions
		left join transports on transports.transport_id = purchases_orders_versions.transport_id
where futures_order in (
	select top 1 futures_order
	from purchases_orders_versions
	where purchase_order_version_id in (select pov.purchase_order_version_id from biweeks_periods_versions bpv inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id inner join purchases_orders_versions pov on bpv.biweek_period_version_id = pov.biweek_period_version_id inner join futures_periods_versions fpv on bpv.version_id = fpv.version_id and fpv.future_period_version_id = @futurePeriodVersionId inner join futures_periods fp on fpv.future_period_id = fp.future_period_id where (bp.start_date + 1) between (fp.start_date) and (fp.end_date) ) and futures_order <> 999
	group by futures_order
	having count(*) > 1
)
order by futures_order desc, biweek_period_version_id, priority, code desc, code_number desc

OPEN cur_purchasesOrders

FETCH NEXT FROM cur_purchasesOrders INTO @purchase_order_version_id, @futures_order

if @@FETCH_STATUS = 0 BEGIN
	update purchases_orders_versions
	set futures_order = @futures_order + 1
	where purchase_order_version_id = @purchase_order_version_id

	CLOSE cur_purchasesOrders
	DEALLOCATE cur_purchasesOrders

	SET @recordDuplicate = 1

	exec sp_po_futures_order_remove_duplicate @futurePeriodVersionId, @DEBUG
end

IF @recordDuplicate = 0 BEGIN
	CLOSE cur_purchasesOrders
	DEALLOCATE cur_purchasesOrders
END

IF @DEBUG = 1
	PRINT 'END:' + @text

--[END] SP section
-------------------------------------------------------
	SET NOCOUNT OFF

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	RETURN 1 --simil boleano a true
END TRY BEGIN CATCH
	DECLARE 
		@ErrorMessage	NVARCHAR(4000), 
		@ErrorNumber	INT, 
		@ErrorSeverity	INT, 
		@ErrorState		INT

	SELECT @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorNumber = ERROR_NUMBER()

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
	RETURN 0 --simil boleano a mal
END CATCH 
